import json
from datetime import datetime
from tempfile import NamedTemporaryFile

import pandas as pd
from django.conf import settings
from django.http import FileResponse, JsonResponse
from django.utils.encoding import escape_uri_path
from rest_framework.views import Response, status
from rest_framework import viewsets, mixins
from rest_framework.filters import OrderingFilter
from django_filters.rest_framework import DjangoFilterBackend
from rest_framework import filters
from rest_framework.permissions import IsAuthenticated
from rest_framework.decorators import action
from django.db.models import F

from apps.common.account.models import Account
from utils.common import Pagination
from utils.data_dict import get_data_dict
from .models import Survey, SurveyAccount, PersonSurveyCategoryResults, SurveyQuestionCategory, SurveyQuestion, \
    SurveyQuestionOption, SurveyAnswer
from .serializers import SurveyListSerializer, SurveyCreateSerializer, SurveyUpdateSerializer, \
    SurveyRetrieveSerializer, SurveyAccountListSerializer, SurveyAnswerUpdateSerializer, SurveyAccountSerializer
from .filters import SurveyFilter, SurveyAccountFilter
from apps.survey_system.survey.utils import personal_category_analysis, download_category_analysis, \
    download_question_answer, download_question_score, person_score_analysis
from apps.survey_system.question.models import Module


from ...common.school.models import Faculty, Major, Classes


# 调查
class SurveyViewSet(mixins.ListModelMixin,
                    mixins.CreateModelMixin,
                    mixins.DestroyModelMixin,
                    mixins.UpdateModelMixin,
                    mixins.RetrieveModelMixin,
                    viewsets.GenericViewSet):
    queryset = Survey.objects.filter(is_active=True).select_related('account')
    filter_backends = (OrderingFilter, DjangoFilterBackend)
    serializer_class = SurveyListSerializer
    filter_class = SurveyFilter
    # permission_classes = [IsAuthenticated]
    pagination_class = Pagination
    ordering_fields = ('id',)

    def filter_queryset(self, queryset):
        """ 动态使用 filter_class """
        if self.action in ['list']:
            for backend in list(self.filter_backends):
                queryset = backend().filter_queryset(self.request, queryset, self)
        return queryset

    def get_serializer_class(self):
        if self.action == 'create':
            return SurveyCreateSerializer
        if self.action in ['update', 'partial_update']:
            return SurveyUpdateSerializer
        if self.action == 'retrieve':
            return SurveyRetrieveSerializer
        return SurveyListSerializer

    def list(self, request, *args, **kwargs):
        queryset = self.filter_queryset(self.get_queryset())

        page = self.paginate_queryset(queryset)
        if page is not None:
            # 找出这些测评下的相关题目和分类
            cate_qs = SurveyQuestionCategory.objects.filter(survey__in=page).values('id', 'survey_id')
            survey_account_qs = SurveyAccount.objects.filter(survey__in=page).values('id', 'survey_id', 'status')
            account_dict = {}
            for i in survey_account_qs:
                if i['survey_id'] not in account_dict:
                    account_dict[i['survey_id']] = 0
                account_dict[i['survey_id']] += 1
            survey_dict = {}
            survey_ids = []
            cate_ids = []
            cate_dict = {}
            for i in cate_qs:
                survey_ids.append(i['survey_id'])
                cate_ids.append(i['id'])
                if i['survey_id'] not in survey_dict:
                    survey_dict[i['survey_id']] = {}
                    survey_dict[i['survey_id']]['children'] = []
                survey_dict[i['survey_id']]['children'].append(i['id'])

            qs = SurveyQuestion.objects.filter(survey_qs_cate_id__in=cate_ids).values('id', 'score', 'survey_qs_cate_id')

            for i in qs:
                if i['survey_qs_cate_id'] not in cate_dict:
                    cate_dict[i['survey_qs_cate_id']] = 0
                cate_dict[i['survey_qs_cate_id']] += i['score']
            survey_score = {}
            for i in survey_ids:
                score = 0
                for j in cate_ids:
                    if j in survey_dict[i]['children']:
                        score += cate_dict[j]
                survey_score[i] = score

            context = {'survey_score': survey_score, 'survey_account_count': account_dict}

            serializer = self.get_serializer(page, many=True, context=context)
            return self.get_paginated_response(serializer.data)

        serializer = self.get_serializer(queryset, many=True)
        return Response(serializer.data)

    def destroy(self, request, *args, **kwargs):
        instance = self.get_object()

        # 判断权限
        if not request.user.is_admin():
            module_codename = instance.module.codename
            if not request.user.had_permission(f'survey_survey_delete_{module_codename}'):
                return Response({'message': '无权限进行该操作'})

        instance.is_active = False
        instance.save()
        return Response(status=status.HTTP_204_NO_CONTENT)

    def create(self, request, *args, **kwargs):
        serializer = self.get_serializer(data=request.data)
        serializer.is_valid(raise_exception=True)

        # 判断权限
        if not request.user.is_admin():
            module_id = serializer.initial_data['module']
            module_codename = Module.objects.filter(id=module_id).values_list('codename', flat=True).first()
            if not request.user.had_permission(f'survey_survey_create_{module_codename}'):
                return Response({'message': '无权限进行该操作'})

        self.perform_create(serializer)
        headers = self.get_success_headers(serializer.data)
        return Response(serializer.data, status=status.HTTP_201_CREATED, headers=headers)

    def update(self, request, *args, **kwargs):
        partial = kwargs.pop('partial', False)
        instance = self.get_object()

        # 判断权限
        if not request.user.is_admin():
            module_codename = instance.module.codename
            if not request.user.had_permission(f'survey_survey_update_{module_codename}'):
                return Response({'message': '无权限进行该操作'})

        serializer = self.get_serializer(instance, data=request.data, partial=partial)
        serializer.is_valid(raise_exception=True)
        self.perform_update(serializer)

        return Response(serializer.data)

    @action(methods=['post'], detail=False, url_path='upload_student')
    def upload_student(self, request, pk=None):
        """ 上传学生的excel文件，返回学生的id列表"""
        excel_file = request.data.get('file', '')
        if not excel_file:
            return Response({"message": "请上传excel文件"}, status=status.HTTP_400_BAD_REQUEST)
        df = pd.read_excel(excel_file, dtype=str, keep_default_na=False)

        columns_lst = list(df.columns)
        # 对列名进行格式化，防止不小心加了空格导致找不到该列
        df.columns = [column.replace(' ', '') for column in columns_lst]
        columns_set = set(df.columns)
        require_columns = {'学号'}
        if not require_columns.issubset(columns_set):  # 判断需要的列名是否存在于传过来的excel表中
            return Response({'message': '请按照模版填写正确的列名'}, status=status.HTTP_400_BAD_REQUEST)
        for column in require_columns:
            df[column] = df[column].str.replace(' ', '')  # 对数据进行格式化，去掉空格

        username_lst = set(df['学号'])
        account_qs = Account.objects.filter(username__in=username_lst).values('username', 'id')
        user_id_lst = [i['id'] for i in account_qs]

        # todo:学号不存在的时候从学校给的数据表找，找不到再返回错误给前端

        res_data = {
            'data': user_id_lst
        }
        return Response(res_data)

    @action(methods=['post'], detail=True, url_path='survey_analysis')
    def survey_analysis(self, request, pk=None):
        """ 问卷分析"""
        try:
            survey_obj = Survey.objects.only('id', 'end_time', 'is_analysis').get(id=pk)
            if survey_obj.end_time > datetime.now():
                return Response({'message': '该场问卷未结束, 不允许分析'}, status=status.HTTP_400_BAD_REQUEST)
            # if survey_obj.is_analysis:
            #     return Response({'message': '该场问卷已经分析'}, status=status.HTTP_400_BAD_REQUEST)

            # 分析每一个维度的结果
            personal_category_analysis(pk)
            survey_obj.is_analysis = True
            survey_obj.save()

            return Response({'message': '分析成功'}, status=status.HTTP_200_OK)
        except Survey.DoesNotExist:
            return Response({'message': '无效的id'}, status=status.HTTP_400_BAD_REQUEST)

    @action(methods=['get'], detail=True, url_path='download_category_analysis')
    def download_category_analysis(self, request, pk=None):
        """ 下载问卷的维度分析"""
        survey_obj = self.get_object()
        # try:
        #     survey_obj = Survey.objects.only('id', 'end_time', 'is_analysis').get(id=pk)
        # except Survey.DoesNotExist:
        #     return Response({'message': '无效的id'}, status=status.HTTP_400_BAD_REQUEST)
        if not survey_obj.is_analysis:
            return Response({'message': '该场问卷还未分析！'}, status=status.HTTP_400_BAD_REQUEST)

        category_qs = SurveyQuestionCategory.objects.filter(survey_id=pk).values()  # 获取维度

        survey_account = SurveyAccount.objects.filter(survey_id=pk).values().order_by('-status')  # 获取该调查的所有人
        account_ids = [i['id'] for i in survey_account]
        # 获取分析结果
        cate_results_qs = PersonSurveyCategoryResults.objects.filter(survey_account_id__in=account_ids)\
            .values('survey_account_id', 'result', 'survey_qs_cate_id', cate_name=F('survey_qs_cate__category_name'))
        account_results_children = {i: {} for i in account_ids}  # 创建该填写对象的分析结果空对象
        for result in cate_results_qs:  # 将分析结果按照填写对象分组
            account_results_children[result['survey_account_id']][result['survey_qs_cate_id']] = result

        for account in survey_account:  # 获取填写对象的分析结果
            account['last_submit_time'] = datetime.strftime(account['last_submit_time'], "%Y-%m-%d %H:%M:%S") \
                if account['last_submit_time'] else None
            account['use_time'] = str(account['use_time']) + '秒' if account['use_time'] else None
            account['cate_result'] = account_results_children[account['id']]

        result = download_category_analysis(category_qs, survey_account, survey_obj.name)

        file_name = f"{survey_obj.name}的维度分析结果.xlsx"

        with NamedTemporaryFile() as tmp:
            result.save(tmp.name)
            file = open(tmp.name, 'rb')
            response = FileResponse(file)
            response['Content-Type'] = "application/octet-stream"
            response['Content-Disposition'] = f"attachment; filename={escape_uri_path(file_name)}"
            response['Access-Control-Expose-Headers'] = 'Content-Disposition'
            return response

    @action(methods=['get'], detail=True, url_path='download_question_answer')
    def download_question_answer(self, request, pk=None):
        """ 下载学生的问题答案"""
        survey_obj = self.get_object()

        # 获取维度
        category_qs = SurveyQuestionCategory.objects.filter(survey_id=pk).values()
        category_ids = []
        cate_children = {}
        cate_question = {}
        for i in category_qs:
            category_ids.append(i['id'])
            cate_children[i['id']] = []
            cate_question[i['id']] = []

        # category_ids = [i['id'] for i in category_qs]

        # 填写对象
        survey_account = SurveyAccount.objects.filter(survey_id=pk, status__in=[1, 2]).values().order_by('-status')
        account_ids = []
        survey_account_dict = {}
        account_children = {}
        account_score = {}
        for i in survey_account:
            i['last_submit_time'] = datetime.strftime(i['last_submit_time'], "%Y-%m-%d %H:%M:%S") if i['last_submit_time'] else None
            i['use_time'] = str(i['use_time']) + '秒' if i['use_time'] else None
            account_ids.append(i['id'])
            survey_account_dict[i['id']] = i
            account_children[i['id']] = {}
            account_score[i['id']] = 0
        # account_ids = [i['id'] for i in survey_account]
        # survey_account_dict = {i['id']: i for i in survey_account}

        # 问题
        question_qs = SurveyQuestion.objects.filter(survey_qs_cate_id__in=category_ids).values()
        question_ids = []
        question_dict = {}
        for i in question_qs:
            question_ids.append(i['id'])
            question_dict[i['id']] = i
            cate_children[i['survey_qs_cate_id']].append(i['id'])
            cate_question[i['survey_qs_cate_id']].append(i)
        # question_ids = [i['id'] for i in question_qs]
        # question_dict = {i['id']: i for i in question_qs}

        # 问题选项
        question_option_qs = SurveyQuestionOption.objects.filter(question_id__in=question_ids).values()
        question_option_dict = {i['id']: i for i in question_option_qs}  # 将选项对象化

        # 答案
        answer_qs = SurveyAnswer.objects.filter(survey_account_id__in=account_ids).values()

        for cate in category_qs:  # 循环维度
            cate_question_ids = cate_children.get(cate['id'], [])  # 获取该维度的问题id
            cate['questions'] = cate_question.get(cate['id'], [])  # 将该维度的问题加入
            account_cate_score = {}
            children = []
            for answer in answer_qs:  # 循环答案，将问题、答案和个人信息加入
                if answer['question_id'] in cate_question_ids:
                    account_score[answer['survey_account_id']] += answer['score']  # 算个人总分

                    if answer['survey_account_id'] not in account_cate_score:  # 算个人维度总分
                        account_cate_score[answer['survey_account_id']] = 0
                    account_cate_score[answer['survey_account_id']] += answer['score']

                    question = question_dict.get(answer['question_id'], {})  # 算维度问题答案
                    answer['question_qs'] = question
                    if question:
                        if question['type'] in [1, 2] or (question['type'] == 4 and question['answer_need_option']):
                            str_ls = []
                            for i in answer['answer_id_lst']:
                                try:
                                    i = int(i)
                                except ValueError:
                                    continue
                                question_option = question_option_dict.get(i, {})
                                str1 = question_option.get('num', '') + '.' + question_option.get('name', '')
                                str_ls.append(str1)
                            answer['answer'] = '、'.join(str_ls)

                        else:
                            answer['answer'] = answer.get('answer_text', '')
                    # answer['account'] = survey_account_dict.get(answer['survey_account_id'])
                    account_children[answer['survey_account_id']][answer['question_id']] = answer['answer']

            for account in survey_account:
                account_dict = account.copy()
                account_dict['children'] = account_children.get(account['id'], {})
                account_dict['cate_score'] = account_cate_score.get(account['id'], 0)
                children.append(account_dict)
                account_children[account['id']] = {}
            cate['children'] = children

        result = download_question_answer(category_qs, account_score)

        file_name = f"{survey_obj.name}的测评填写答案.xlsx"

        with NamedTemporaryFile() as tmp:
            result.save(tmp.name)
            file = open(tmp.name, 'rb')
            response = FileResponse(file)
            response['Content-Type'] = "application/octet-stream"
            response['Content-Disposition'] = f"attachment; filename={escape_uri_path(file_name)}"
            response['Access-Control-Expose-Headers'] = 'Content-Disposition'
            return response

    @action(methods=['get'], detail=True, url_path='download_question_score')
    def download_question_score(self, request, pk=None):
        """ 下载学生的问题分数"""
        survey_obj = self.get_object()

        # 获取维度
        category_ids = SurveyQuestionCategory.objects.filter(survey_id=pk).values_list('id', flat=True)

        # 填写对象
        survey_account = SurveyAccount.objects.filter(survey_id=pk, status__in=[1, 2]).values().order_by('-status')
        account_ids = []
        survey_account_dict = {}
        account_children = {}
        account_score = {}
        for i in survey_account:
            i['last_submit_time'] = datetime.strftime(i['last_submit_time'], "%Y-%m-%d %H:%M:%S") if i[
                'last_submit_time'] else None
            i['use_time'] = str(i['use_time']) + '秒' if i['use_time'] else None
            account_ids.append(i['id'])
            survey_account_dict[i['id']] = i
            account_children[i['id']] = {}
            account_score[i['id']] = 0

        # 问题
        question_qs = SurveyQuestion.objects.filter(survey_qs_cate_id__in=category_ids).values()
        question_ids = []
        question_dict = {}
        for i in question_qs:
            question_ids.append(i['id'])
            question_dict[i['id']] = i

        # 答案
        answer_qs = SurveyAnswer.objects.filter(survey_account_id__in=account_ids).values()


        for answer in answer_qs:  # 循环答案，将问题、答案和个人信息加入
            account_score[answer['survey_account_id']] += answer['score']  # 算个人总分
            account_children[answer['survey_account_id']][answer['question_id']] = answer['score']

        for account in survey_account:
            account['all_score'] = account_score.get(account['id'], 0)
            account['children'] = account_children.get(account['id'], {})

        result = download_question_score(survey_account, question_qs, survey_obj.name)

        file_name = f"{survey_obj.name}的测评填得分.xlsx"

        with NamedTemporaryFile() as tmp:
            result.save(tmp.name)
            file = open(tmp.name, 'rb')
            response = FileResponse(file)
            response['Content-Type'] = "application/octet-stream"
            response['Content-Disposition'] = f"attachment; filename={escape_uri_path(file_name)}"
            response['Access-Control-Expose-Headers'] = 'Content-Disposition'
            return response

    @action(methods=['get'], detail=True, url_path='survey_faculty')
    def get_survey_faculty(self, request, pk=None):
        faculty_qs = SurveyAccount.objects.filter(survey_id=pk).values('faculty_code', 'faculty_title').distinct()
        return Response(faculty_qs)

    @action(methods=['get'], detail=True, url_path='survey_class')
    def get_survey_class(self, request, pk=None):
        class_qs = SurveyAccount.objects.filter(survey_id=pk).values('class_code', 'class_title').distinct()
        return Response(class_qs)

    @action(methods=['get'], detail=True, url_path='survey_question')
    def get_survey_question(self, request, pk=None):
        """
            获取调查问卷问题
            格式：
            [
                { id: 1, name: '题目1', type: 1, options: [{ name: '选项A', id: 1, file:[] }, { name: '选项B', id: 2 }] },
                { id: 2, name: '题目2', type: 2, options: [{ name: '选项A', id: 3, file:[] }, { name: '选项B', id: 4 }] }
            ]

        """

        cate_objs = SurveyQuestionCategory.objects.filter(survey_id=pk).values()
        cate_ids = [i['id'] for i in cate_objs]
        question_qs = SurveyQuestion.objects.filter(survey_qs_cate_id__in=cate_ids, type__in=[1, 2]).values('id', 'name', 'type')

        question_ids = []
        question_children = {}
        for i in question_qs:
            question_ids.append(i['id'])
            if i['id'] not in question_children:
                question_children[i['id']] = []

        question_option_qs = SurveyQuestionOption.objects.filter(question_id__in=question_ids)\
            .values('id', 'question_id', 'file', 'name')

        for i in question_option_qs:  # 给每个选项的文件序列化
            if i['file']:
                for j in i['file']:
                    file_path = j['url']
                    full_file_path = f"{settings.FILE_URL}{file_path}" if 'http' not in file_path else file_path
                    file_type = j['content_type'].split('/')[0]
                    file_type = file_type.lower()
                    j['full_url'] = full_file_path
                    j['file_type'] = file_type
            question_children[i['question_id']].append(i)  # 方便获取每个问题的选项

        for i in question_qs:
            i['options'] = question_children[i['id']]

        return Response(question_qs)

    @action(methods=['get'], detail=True, url_path='survey_file_question')
    def get_survey_file_question(self, request, pk=None):
        """
            获取调查问卷问题
            格式：
            [
                { id: 1, name: '题目1', type: 1, options: [{ name: '选项A', id: 1, file:[] }, { name: '选项B', id: 2 }] },
                { id: 2, name: '题目2', type: 2, options: [{ name: '选项A', id: 3, file:[] }, { name: '选项B', id: 4 }] }
            ]

        """

        cate_objs = SurveyQuestionCategory.objects.filter(survey_id=pk).values()
        cate_ids = [i['id'] for i in cate_objs]
        question_qs = SurveyQuestion.objects.filter(survey_qs_cate_id__in=cate_ids, type__in=[4], answer_need_option=True)\
            .values('id', 'name', 'type', 'option_type')

        question_ids = []
        question_children = {}
        for i in question_qs:
            question_ids.append(i['id'])
            if i['id'] not in question_children:
                question_children[i['id']] = []

        question_option_qs = SurveyQuestionOption.objects.filter(question_id__in=question_ids) \
            .values('id', 'question_id', 'file', 'name')

        for i in question_option_qs:  # 给每个选项的文件序列化
            if i['file']:
                for j in i['file']:
                    file_path = j['url']
                    full_file_path = f"{settings.FILE_URL}{file_path}" if 'http' not in file_path else file_path
                    file_type = j['content_type'].split('/')[0]
                    file_type = file_type.lower()
                    j['full_url'] = full_file_path
                    j['file_type'] = file_type
            question_children[i['question_id']].append(i)  # 方便获取每个问题的选项

        for i in question_qs:
            i['options'] = question_children[i['id']]

        return Response(question_qs)

    @action(methods=['get'], detail=True, url_path='survey_questions')
    def get_survey_questions(self, request, pk=None):
        """
            获取调查问卷问题
            格式：
            [
                { id: 1, name: '题目1', type: 1, },
                { id: 2, name: '题目2', type: 2, }
            ]

        """

        cate_objs = SurveyQuestionCategory.objects.filter(survey_id=pk).values()
        cate_ids = [i['id'] for i in cate_objs]
        question_qs = SurveyQuestion.objects.filter(survey_qs_cate_id__in=cate_ids, type__in=[1, 2]).values('id', 'name')

        question_ls = list(question_qs)
        question_ls.insert(0, {'id': -1, 'name': '性别'})

        return Response(question_ls)


# 调查
class SurveyAccountViewSet(mixins.ListModelMixin,
                           viewsets.GenericViewSet):
    queryset = SurveyAccount.objects.all().select_related('survey')
    filter_backends = (OrderingFilter, DjangoFilterBackend)
    serializer_class = SurveyAccountListSerializer
    filter_class = SurveyAccountFilter
    # permission_classes = [IsAuthenticated]
    pagination_class = Pagination
    ordering_fields = ('id',)

    def filter_queryset(self, queryset):
        """ 动态使用 filter_class """
        if self.action in ['list', 'get_answer_situation']:
            for backend in list(self.filter_backends):
                queryset = backend().filter_queryset(self.request, queryset, self)
        return queryset

    @action(methods=['post'], detail=False, url_path='answer_account')
    def get_answer_account(self, request, pk=None):
        survey = request.query_params.get('survey', None)
        print(survey)
        queryset = self.filter_queryset(self.get_queryset()).filter(status__in=[1, 2], survey_id=survey)
        answer_id_ls = request.data.get('answer_id_ls', [])
        print(answer_id_ls)
        # answer_id_ls = json.loads(answer_id_ls)
        if answer_id_ls:
            print(answer_id_ls)
            survey_account_ids = SurveyAnswer.objects.filter(answer_id_lst=answer_id_ls)\
                .values_list('survey_account_id', flat=True)
            print(survey_account_ids)
            queryset = queryset.filter(id__in=list(survey_account_ids))
        page = self.paginate_queryset(queryset)
        if page is not None:
            # 找出这些测评下的相关题目和分类
            cate_ids = SurveyQuestionCategory.objects.filter(survey_id=survey).values_list('id', flat=True)
            qs = SurveyQuestion.objects.filter(survey_qs_cate_id__in=cate_ids).values()
            zg_question_ids = []
            zg_question_dict = {}
            for i in qs:
                if i['type'] in [3, 4]:
                    zg_question_ids.append(i['id'])
                    zg_question_dict[i['id']] = i
            survey_answer_qs = SurveyAnswer.objects.filter(survey_account__in=page).values()

            account_data = {}

            for i in survey_answer_qs:
                if i['survey_account_id'] not in account_data:
                    account_data[i['survey_account_id']] = {}
                    account_data[i['survey_account_id']]['sum_score'] = 0
                    account_data[i['survey_account_id']]['fzg_score'] = 0
                    account_data[i['survey_account_id']]['zg'] = {}
                account_data[i['survey_account_id']]['sum_score'] += i['score']
                if i['question_id'] not in zg_question_ids:
                    account_data[i['survey_account_id']]['fzg_score'] += i['score']
                else:
                    zg_answer = {
                        'score': i['score'],
                        'answer': i['answer_text'],
                        'is_corrected': i['is_corrected'],
                        'file': i['file'],
                        'id': i['id'],
                        'answer_ls': i['answer_id_lst']
                    }
                    account_data[i['survey_account_id']]['zg'][i['question_id']] = zg_answer
            options_qs = SurveyQuestionOption.objects.filter(question_id__in=zg_question_ids,
                                                             question__answer_need_option=True)\
                .values('id', 'num', 'name')
            options_dict = {}
            for qs in options_qs:
                options_dict[qs['id']] = qs
            context = {'account_data': account_data, 'zg_question_ids': zg_question_ids, 'options_dict': options_dict}
            serializer = self.get_serializer(page, many=True, context=context)
            return self.get_paginated_response(serializer.data)

        serializer = self.get_serializer(queryset, many=True)
        return Response(serializer.data)

    @action(methods=['get'], detail=False, url_path='answer_situation')
    def get_answer_situation(self, request, *args, **kwargs):
        queryset = self.filter_queryset(self.get_queryset())
        page = self.paginate_queryset(queryset)
        if page is not None:
            # 找出这些测评下的相关题目和分类
            survey_answer_qs = SurveyAnswer.objects.filter(survey_account__in=page).values()
            account_data = {}
            for i in survey_answer_qs:
                if i['survey_account_id'] not in account_data:
                    account_data[i['survey_account_id']] = 0
                account_data[i['survey_account_id']] += i['score']
            context = {'account_data': account_data}
            serializer = SurveyAccountSerializer(page, many=True, context=context)
            return self.get_paginated_response(serializer.data)

        serializer = SurveyAccountSerializer(queryset, many=True)
        return Response(serializer.data)

    # 每个人两份问卷的总分
    @action(methods=['get'], detail=False, url_path='two_survey_score')
    def get_two_survey_score(self, request, *args, **kwargs):
        first_survey_id = request.query_params.get('first_survey_id', None)
        second_survey_id = request.query_params.get('second_survey_id', None)

        if not all([first_survey_id, second_survey_id]):
            return Response({'message': '缺少参数'}, status=status.HTTP_400_BAD_REQUEST)

        if first_survey_id == second_survey_id:
            return Response({'message': '需两份不同的测评！'}, status=status.HTTP_400_BAD_REQUEST)

        # 第一个问卷
        first_category_ids = SurveyQuestionCategory.objects.filter(survey_id=first_survey_id).values_list('id', flat=True)
        first_question_ids = SurveyQuestion.objects.filter(survey_qs_cate_id__in=list(first_category_ids)) \
            .values_list('id', flat=True)

        # 第二个问卷
        second_category_ids = SurveyQuestionCategory.objects.filter(survey_id=second_survey_id).values_list('id',
                                                                                                          flat=True)
        second_question_ids = SurveyQuestion.objects.filter(survey_qs_cate_id__in=list(second_category_ids)) \
            .values_list('id', flat=True)

        first_answer_qs = SurveyAnswer.objects.filter(question_id__in=first_question_ids)\
            .values('score', username=F('survey_account__username'))
        second_answer_qs = SurveyAnswer.objects.filter(question_id__in=second_question_ids)\
            .values('score', username=F('survey_account__username'))

        first_survey_score = {}
        for i in first_answer_qs:
            if i['username'] not in first_survey_score:
                first_survey_score[i['username']] = 0
            first_survey_score[i['username']] += i['score']

        second_survey_score = {}
        for i in second_answer_qs:
            if i['username'] not in second_survey_score:
                second_survey_score[i['username']] = 0
            second_survey_score[i['username']] += i['score']

        queryset = SurveyAccount.objects.filter(survey_id=first_survey_id).values()
        for i in queryset:
            i['first_survey_score'] = first_survey_score.get(i['username'], 0)
            i['second_survey_score'] = second_survey_score.get(i['username'], 0)

        # 分页
        page = request.query_params.get('page', None)
        limit = request.query_params.get('limit', None)
        try:
            page = int(page)
            limit = int(limit)
        except:
            page, limit = None, None
        if page and limit:
            total = len(queryset)  # 数据总量
            data = queryset[(page - 1) * limit: page * limit]
            return Response({'results': data, 'count': total})
        else:
            return Response(queryset)

    # 某个人两份问卷的对比
    @action(methods=['get'], detail=False, url_path='two_survey_analysis')
    def get_two_survey_analysis(self, request, *args, **kwargs):
        first_survey_id = request.query_params.get('first_survey_id', None)
        second_survey_id = request.query_params.get('second_survey_id', None)
        survey_account_id = request.query_params.get('survey_account_id', None)
        if not all([first_survey_id, survey_account_id, survey_account_id]):
            return Response({'message': '缺少参数'}, status=status.HTTP_400_BAD_REQUEST)

        try:
            first_survey = Survey.objects.get(id=first_survey_id)
            second_survey = Survey.objects.get(id=second_survey_id)
        except Survey.DoesNotExist:
            return Response({'message': '该测评不存在'}, status=status.HTTP_400_BAD_REQUEST)

        try:
            first_survey_account = SurveyAccount.objects.get(id=survey_account_id)
        except SurveyAccount.DoesNotExist:
            return Response({'message': '第一场测评的该测评对象不存在'}, status=status.HTTP_400_BAD_REQUEST)

        if first_survey_account.status == 0:
            return Response({'message': '第一场测评的该测评对象未填写'}, status=status.HTTP_400_BAD_REQUEST)

        survey_account_qs = SurveyAccount.objects.filter(survey_id=second_survey_id,
                                                         username=first_survey_account.username).first()
        if not survey_account_qs:
            return Response({'message': '第二场测评的该测评对象不存在'}, status=status.HTTP_400_BAD_REQUEST)

        if survey_account_qs.status == 0:
            return Response({'message': '第二场测评的该测评对象未填写'}, status=status.HTTP_400_BAD_REQUEST)

        first_data = person_score_analysis(first_survey_id, survey_account_id)
        second_data = person_score_analysis(second_survey_id, survey_account_qs.id)

        delete_data = ['创造']  # 临时创建的删除数据

        for i in first_data:
            if i[0] in delete_data:
                first_data.remove(i)

        for i in second_data:
            if i[0] in delete_data:
                second_data.remove(i)

        data = {
            'first_data':{
                'survey_name': first_survey.name,
                'data': first_data
            },
            'second_data':{
                'survey_name': second_survey.name,
                'data': second_data
            },
        }
        return Response(data)


class SurveyAnswerViewSet(mixins.UpdateModelMixin,
                          viewsets.GenericViewSet):
    queryset = SurveyAnswer.objects.all().select_related('question')
    serializer_class = SurveyAnswerUpdateSerializer

    def update(self, request, *args, **kwargs):
        partial = kwargs.pop('partial', False)
        instance = self.get_object()
        serializer = self.get_serializer(instance, data=request.data, partial=partial)
        serializer.is_valid(raise_exception=True)
        self.perform_update(serializer)

        if getattr(instance, '_prefetched_objects_cache', None):
            # If 'prefetch_related' has been applied to a queryset, we need to
            # forcibly invalidate the prefetch cache on the instance.
            instance._prefetched_objects_cache = {}

        return Response(serializer.data)


class SurveyAnalysisResultsViewSet(viewsets.GenericViewSet):
    queryset = Survey.objects.filter(is_active=True)

    # 单个问题的填写比例分析
    @action(methods=['post'], detail=True, url_path='survey_analysis')
    def get_survey_analysis(self, request, pk=None):
        survey_obj = self.get_object()

        username = request.data.get('username', '')
        faculty_code = request.data.get('faculty_code', '')
        class_code = request.data.get('class_code', '')
        sex = request.data.get('sex', '')
        question = request.data.get('question', [])
        # if question:
        #     question = json.loads(question)

        # 填写对象
        survey_account = SurveyAccount.objects.filter(survey_id=pk, status__in=[1, 2])
        if username:
            survey_account = survey_account.filter(username=username)
        if faculty_code:
            survey_account = survey_account.filter(faculty_code=faculty_code)
        if class_code:
            survey_account = survey_account.filter(class_code=class_code)
        if sex:
            survey_account = survey_account.filter(sex=sex)
        if question:
            question_ids = []
            question_dict = {}
            for i in question:
                question_dict[i['question_id']] = i
                question_ids.append(i['question_id'])
            answer_qs = SurveyAnswer.objects.filter(question_id__in=question_ids)\
                .values('answer_id_lst', 'survey_account_id', 'question_id')

            answer_dict = {}
            account_id_set = set()
            for qs in answer_qs:
                account_id_set.add(qs['survey_account_id'])
                if qs['survey_account_id'] not in answer_dict:
                    answer_dict[qs['survey_account_id']] = {}
                answer_dict[qs['survey_account_id']][qs['question_id']] = qs['answer_id_lst']

            account_ids_ls = []
            for i in account_id_set:
                account_answer = answer_dict[i]
                flag = 1
                for qt in question:
                    if qt['question_type'] == 1:
                        if str(qt['check_option']) not in account_answer[qt['question_id']]:
                            flag = 0
                            break
                    elif qt['question_type'] == 2:
                        for option_id in qt['check_option_lst']:
                            if str(option_id) not in account_answer[qt['question_id']]:
                                flag = 0
                                break
                if flag == 1:
                    account_ids_ls.append(i)

            if account_ids_ls:
                survey_account = survey_account.filter(id__in=account_ids_ls)
            else:
                survey_account = survey_account.none()

        survey_account = survey_account.values().order_by('-status')

        if not survey_account:
            return Response({'message': '该场问卷还没有符合条件的人填写！'}, status=status.HTTP_400_BAD_REQUEST)

        account_ids = []
        survey_account_dict = {}
        account_children = {}
        for i in survey_account:
            account_ids.append(i['id'])
            survey_account_dict[i['id']] = i
            account_children[i['id']] = {}

        # 获取维度
        category_qs = SurveyQuestionCategory.objects.filter(survey_id=pk).values()
        category_ids = []
        cate_children = {}
        cate_question = {}
        for i in category_qs:
            category_ids.append(i['id'])
            cate_children[i['id']] = []
            cate_question[i['id']] = []

        # 问题
        question_qs = SurveyQuestion.objects.filter(survey_qs_cate_id__in=category_ids, type__in=[1, 2]) \
            .values('id', 'name', 'survey_qs_cate_id', 'type', 'file', 'answer_need_option')
        question_ids = []
        question_dict = {}
        question_children = {}
        for i in question_qs:
            question_ids.append(i['id'])
            question_dict[i['id']] = i
            cate_children[i['survey_qs_cate_id']].append(i['id'])
            cate_question[i['survey_qs_cate_id']].append(i)
            question_children[i['id']] = []

        # 问题选项
        question_option_qs = SurveyQuestionOption.objects.filter(question_id__in=question_ids) \
            .values('id', 'name', 'question_id', 'is_answer')
        question_option_dict = {}
        for i in question_option_qs:  # 将选项对象化
            question_option_dict[i['id']] = i
            question_children[i['question_id']].append(i)

        # 答案
        answer_qs = SurveyAnswer.objects.filter(survey_account_id__in=account_ids).values()
        answer_dict = {}
        for i in answer_qs:
            if i['question_id'] not in answer_dict:
                answer_dict[i['question_id']] = []
            answer_dict[i['question_id']].append(i)

        answers_analysis = []
        for question in question_qs:
            question_answer = answer_dict[question['id']]

            question_type = get_data_dict('question_type', default_value={})
            question['question_type'] = question_type.get(question['type'], 'None')

            for file in question['file']:
                file_path = file['url']
                full_file_path = f"{settings.FILE_URL}{file_path}"
                file_type = file['content_type'].split('/')[0]
                file_type = file_type.lower()
                file['full_path'] = full_file_path
                file['file_type'] = file_type

            question['question_children'] = []
            question_people = 0
            score = 0
            if question['type'] in [1, 2] or (question['type'] == 4 and question['answer_need_option']):
                bargraph_source = [['选项', '人数', '比例']]
                option_dict = {}
                for answer in question_answer:
                    score += answer['score']
                    if answer['answer_id_lst']:
                        question_people += 1
                        for answer_id in answer['answer_id_lst']:
                            try:
                               answer_id = int(answer_id)
                            except ValueError:
                                continue
                            if answer_id not in option_dict:
                                option_dict[answer_id] = 0
                            option_dict[answer_id] += 1
                question_options = question_children[question['id']]
                for option in question_options:

                    option_people = option_dict.get(option['id'], 0)
                    if question_people != 0:
                      option_rate = float("%.2f" % ((option_people / question_people) * 100))
                    else:
                        option_rate = 0
                    option['option_people'] = option_people
                    option['option_rate'] = option_rate
                    question['question_children'].append(option)
                    option_ls = [option['name'], option_people, option_rate]
                    bargraph_source.append(option_ls)
                question['barchart_source'] = bargraph_source

            # elif question['type'] == 3:
            #     for answer in question_answer:
            #
            #         score += answer['score']
            #         if answer['answer_text']:
            #             question_people += 1
            #             question['question_children'].append(answer['answer_text'])
            if question_people != 0:
                question_avg = round(score / question_people, 1)
            else:
                question_avg = 0
            question['question_avg'] = question_avg
            question['question_people'] = question_people

            answers_analysis.append(question)
            question['activeName'] = f"{question['id']}-table"    # 前端需要的字段

        res_data = {'data': answers_analysis, 'title': f"{survey_obj.name}的题目分析"}
        return Response(res_data, status=status.HTTP_200_OK)

    # 单个属性的整体分析
    @action(methods=['post'], detail=True, url_path='survey_avg_analysis')
    def get_avg_survey_analysis(self, request, pk=None):
        survey_obj = self.get_object()

        # 基本信息获取
        faculty_qs = Faculty.objects.values()
        faculty_dict = {i['faculty_code']: i['faculty_title'] for i in faculty_qs}

        major_qs = Major.objects.values()
        major_dict = {i['major_code']: i['major_title'] for i in major_qs}

        class_qs = Classes.objects.values()
        class_dict = {i['class_code']: i['class_name'] for i in class_qs}

        # 问卷问题个数
        category_qs = SurveyQuestionCategory.objects.filter(survey_id=pk).values('id', 'category_name')
        category_ids = []
        category_dict = {}
        category_qt_count = {}
        cate_score = {}
        for cate in category_qs:
            category_ids.append(cate['id'])
            category_dict[cate['id']] = cate['category_name']
            category_qt_count[cate['id']] = 0
            cate_score[cate['id']] = 0

        question_qs = SurveyQuestion.objects.filter(survey_qs_cate_id__in=category_ids) \
            .values('id', 'survey_qs_cate_id', 'name')

        question_count = 0  # 题目数
        question_cate_dict = {}
        for qt in question_qs:
            question_count += 1
            question_cate_dict[qt['id']] = qt['survey_qs_cate_id']
            category_qt_count[qt['survey_qs_cate_id']] += 1


        # # 问卷问题个数
        # category_ids = SurveyQuestionCategory.objects.filter(survey_id=pk).values_list('id', flat=True)
        # questions_count = SurveyQuestion.objects.filter(survey_qs_cate_id__in=category_ids).count()

        faculty_code = request.data.get('faculty_code', '')
        class_code = request.data.get('class_code', '')
        sex = request.data.get('sex', '')

        # 填写对象
        survey_account = SurveyAccount.objects.filter(survey_id=pk, status__in=[1, 2])
        if faculty_code:
            survey_account = survey_account.filter(faculty_code=faculty_code)
        if class_code:
            survey_account = survey_account.filter(class_code=class_code)
        if sex:
            survey_account = survey_account.filter(sex=sex)

        # 填写对象
        survey_account = survey_account.values()

        survey_account_dict = {}
        survey_account_ids = []
        faculty_code_dict = {}
        major_code_dict = {}
        class_code_dict = {}
        sex_dict = {}
        grade_dict = {}
        all_people = 0

        for qs in survey_account:
            all_people += 1
            survey_account_ids.append(qs['id'])
            survey_account_dict[qs['id']] = qs

            if qs['faculty_code'] not in faculty_code_dict:
                faculty_code_dict[qs['faculty_code']] = {}
                faculty_code_dict[qs['faculty_code']]['people_count'] = 0
                # faculty_code_dict[qs['faculty_code']]['all_score'] = 0
                for cate_id in category_ids:  # 添加每一个学院的每个维度的总分
                    faculty_code_dict[qs['faculty_code']][cate_id] = 0
            faculty_code_dict[qs['faculty_code']]['people_count'] += 1

            if qs['major_code'] not in major_code_dict:
                major_code_dict[qs['major_code']] = {}
                major_code_dict[qs['major_code']]['people_count'] = 0
                # major_code_dict[qs['major_code']]['all_score'] = 0
                for cate_id in category_ids:  # 添加每一个专业的每个维度的总分
                    major_code_dict[qs['major_code']][cate_id] = 0
            major_code_dict[qs['major_code']]['people_count'] += 1

            if qs['class_code'] not in class_code_dict:
                class_code_dict[qs['class_code']] = {}
                class_code_dict[qs['class_code']]['people_count'] = 0
                # class_code_dict[qs['class_code']]['all_score'] = 0
                for cate_id in category_ids:  # 添加每一个班级的每个维度的总分
                    class_code_dict[qs['class_code']][cate_id] = 0
            class_code_dict[qs['class_code']]['people_count'] += 1

            if qs['sex'] not in sex_dict:
                sex_dict[qs['sex']] = {}
                sex_dict[qs['sex']]['people_count'] = 0
                # sex_dict[qs['sex']]['all_score'] = 0
                for cate_id in category_ids:  # 添加每一个性别的每个维度的总分
                    sex_dict[qs['sex']][cate_id] = 0
            sex_dict[qs['sex']]['people_count'] += 1

            if qs['grade_name'] not in grade_dict:
                grade_dict[qs['grade_name']] = {}
                grade_dict[qs['grade_name']]['people_count'] = 0
                # grade_dict[qs['grade_name']]['all_score'] = 0
                for cate_id in category_ids:  # 添加每一个班级的每个维度的总分
                    grade_dict[qs['grade_name']][cate_id] = 0
            grade_dict[qs['grade_name']]['people_count'] += 1

        survey_answer_qs = SurveyAnswer.objects.filter(survey_account_id__in=survey_account_ids)\
            .values('survey_account_id', 'score', 'question_id')

        for qs in survey_answer_qs:
            # faculty_code_dict[survey_account_dict[qs['survey_account_id']]['faculty_code']]['all_score'] += qs['score']
            # major_code_dict[survey_account_dict[qs['survey_account_id']]['major_code']]['all_score'] += qs['score']
            # class_code_dict[survey_account_dict[qs['survey_account_id']]['class_code']]['all_score'] += qs['score']
            # sex_dict[survey_account_dict[qs['survey_account_id']]['sex']]['all_score'] += qs['score']
            # grade_dict[survey_account_dict[qs['survey_account_id']]['grade_name']]['all_score'] += qs['score']

            cate_id = question_cate_dict[qs['question_id']]
            cate_score[cate_id] += qs['score']

            faculty_code_dict[survey_account_dict[qs['survey_account_id']]['faculty_code']][cate_id] += qs['score']
            major_code_dict[survey_account_dict[qs['survey_account_id']]['major_code']][cate_id] += qs['score']
            class_code_dict[survey_account_dict[qs['survey_account_id']]['class_code']][cate_id] += qs['score']
            sex_dict[survey_account_dict[qs['survey_account_id']]['sex']][cate_id] += qs['score']
            grade_dict[survey_account_dict[qs['survey_account_id']]['grade_name']][cate_id] += qs['score']


        faculty_data = ['学院']
        major_data= ['专业']
        class_data = ['班级']
        grade_data = ['年级']
        sex_data = ['性别']
        for cate_id in category_ids:
            cate_name = category_dict[cate_id]
            faculty_data.append(cate_name)
            major_data.append(cate_name)
            class_data.append(cate_name)
            grade_data.append(cate_name)
            sex_data.append(cate_name)

        faculty = {
            'id': 'faculty',
            'name': '各个学院各个维度的均分',
            'activeName': 'faculty-barchart',
            'data': [],
            'end': 15
        }

        faculty['data'].append(faculty_data)

        for key in faculty_code_dict.keys():
            faculty_name = faculty_dict.get(key, '未知')
            person_count = faculty_code_dict[key]['people_count']
            faculty_one_data = [faculty_name]
            for cate_id in category_ids:
                qt_count = category_qt_count[cate_id]
                divisor = qt_count * person_count
                faculty_score = faculty_code_dict[key][cate_id]
                if divisor:
                    avg_score = "%.2f" % (faculty_score / divisor)
                else:
                    avg_score = 0
                faculty_one_data.append(avg_score)
            faculty['data'].append(faculty_one_data)

        major = {
            'id': 'major',
            'name': '各个专业各个维度的均分',
            'activeName': 'major-barchart',
            'data': [],
            'end': 15
        }

        major['data'].append(major_data)

        for key in major_code_dict.keys():
            major_name = major_dict.get(key, '未知')
            person_count = major_code_dict[key]['people_count']
            major_one_data = [major_name]
            for cate_id in category_ids:
                qt_count = category_qt_count[cate_id]
                divisor = qt_count * person_count
                major_score = major_code_dict[key][cate_id]
                if divisor:
                    avg_score = "%.2f" % (major_score / divisor)
                else:
                    avg_score = 0
                major_one_data.append(avg_score)
            major['data'].append(major_one_data)

        classes = {
            'id': 'classes',
            'name': '各个班级各个维度的均分',
            'activeName': 'classes-barchart',
            'data': [],
            'end': 15
        }

        classes['data'].append(class_data)

        # class_avg = [['班级', '均分', '人数']]
        for key in class_code_dict.keys():
            class_name = class_dict.get(key, '未知')
            person_count = class_code_dict[key]['people_count']
            class_one_data = [class_name]
            for cate_id in category_ids:
                qt_count = category_qt_count[cate_id]
                divisor = qt_count * person_count
                class_score = class_code_dict[key][cate_id]
                if divisor:
                    avg_score = "%.2f" % (class_score / divisor)
                else:
                    avg_score = 0
                class_one_data.append(avg_score)
            classes['data'].append(class_one_data)

        sex = {
            'id': 'sex',
            'name': '性别各个维度的均分',
            'activeName': 'sex-barchart',
            'data': []
        }

        sex['data'].append(sex_data)

        # sex_avg = [['性别', '均分', '人数']]
        for key in sex_dict.keys():
            person_count = sex_dict[key]['people_count']
            sex_one_data = [key]
            for cate_id in category_ids:
                qt_count = category_qt_count[cate_id]
                divisor = qt_count * person_count
                sex_score = sex_dict[key][cate_id]
                if divisor:
                    avg_score = "%.2f" % (sex_score / divisor)
                else:
                    avg_score = 0
                sex_one_data.append(avg_score)
            sex['data'].append(sex_one_data)

        grade = {
            'id': 'grade',
            'name': '各个年级各个维度的均分',
            'activeName': 'grade-barchart',
            'data': []
        }

        grade['data'].append(grade_data)

        # grade_avg = [['年级', '均分', '人数']]
        for key in grade_dict.keys():
            person_count = grade_dict[key]['people_count']
            grade_one_data = [key]
            for cate_id in category_ids:
                qt_count = category_qt_count[cate_id]
                divisor = qt_count * person_count
                grade_score = grade_dict[key][cate_id]
                if divisor:
                    avg_score = "%.2f" % (grade_score / divisor)
                else:
                    avg_score = 0
                grade_one_data.append(avg_score)
            grade['data'].append(grade_one_data)

        cate = {
            'id': 'cate',
            'name': '各维度的均分',
            'activeName': 'cate-barchart',
            'data': [['维度', '均分', '题目数']]
        }
        for cate_id in category_ids:
            name = category_dict[cate_id]
            all_score = cate_score[cate_id]
            qt_count = category_qt_count[cate_id]
            divisor = qt_count * all_people
            if divisor:
                avg_score = "%.2f" % (all_score / divisor)
            else:
                avg_score = 0
            data = [name, avg_score, qt_count]
            cate['data'].append(data)

        data = list([cate, grade, faculty, major, classes, sex])
        res_data = {'data': data, 'title': f"{survey_obj.name}的总体分析"}
        return Response(res_data, status=status.HTTP_200_OK)

    # 两个问题的组合分析
    @action(methods=['post'], detail=True, url_path='survey_group_avg_analysis')
    def get_survey_group_avg_analysis(self, request, pk=None):
        survey_obj = self.get_object()

        # 基本信息获取
        faculty_code = request.data.get('faculty_code', '')  # 过滤数据
        class_code = request.data.get('class_code', '')

        group = request.data.get('group', '')  # 组合数据 sex
        question_ids = request.data.get('question_ids', [])
        if not question_ids:
            return Response({'message': '缺少参数'}, status=status.HTTP_400_BAD_REQUEST)

        # 填写对象
        survey_account = SurveyAccount.objects.filter(survey_id=pk, status__in=[1, 2])
        if faculty_code:
            survey_account = survey_account.filter(faculty_code=faculty_code)
        if class_code:
            survey_account = survey_account.filter(class_code=class_code)

        survey_account = survey_account.values().order_by('-status')

        if group == 'sex':
            group_qs = SurveyAccount.objects.filter(survey_id=pk).values_list('sex', flat=True).distinct()

        else:
            option = SurveyQuestionOption.objects.filter(question_id=question_ids[1]).values('id', 'num', 'name')
            option_dict1 = {}
            group_qs = []
            for op in option:
                group_qs.append(op['id'])
                option_dict1[op['id']] = op

        if question_ids:
            option0 = SurveyQuestionOption.objects.filter(question_id=question_ids[0]).values('id', 'num', 'name')
            option_dict0 = {}
            question_option = []
            for op in option0:
                question_option.append(op['id'])
                option_dict0[op['id']] = op
        else:
            question_option = []

        data_dict = {}
        for i in group_qs:
            for j in question_option:
                tuple_key = (i, j)
                data_dict[tuple_key] = {}
                data_dict[tuple_key]['all_score'] = 0
                data_dict[tuple_key]['people_count'] = 0

        account_ids = []
        for i in survey_account:
            account_ids.append(i['id'])

        # 答案
        answer_qs = SurveyAnswer.objects.filter(survey_account_id__in=account_ids).select_related('survey_account')\
            .values('survey_account_id', 'question_id', 'answer_id_lst', 'score',
                    class_code=F('survey_account__class_code'), faculty_code=F('survey_account__faculty_code'),
                    sex=F('survey_account__sex'))
        answer_dict = {}

        if group == 'sex':
            for i in answer_qs:
                if i['survey_account_id'] not in answer_dict:
                    answer_dict[i['survey_account_id']] = {}
                    answer_dict[i['survey_account_id']]['all_score'] = 0
                    answer_dict[i['survey_account_id']]['tuple_key'] = []
                answer_dict[i['survey_account_id']]['all_score'] += i['score']

                if i['question_id'] in question_ids:
                    for j in i['answer_id_lst']:
                        try:
                            j = int(j)
                        except ValueError:
                            continue
                        tuple_key = (i['sex'], j)
                        answer_dict[i['survey_account_id']]['tuple_key'].append(tuple_key)

        else:
            for i in answer_qs:
                if i['survey_account_id'] not in answer_dict:
                    answer_dict[i['survey_account_id']] = {}
                    answer_dict[i['survey_account_id']]['all_score'] = 0
                    answer_dict[i['survey_account_id']]['tuple_key'] = []
                    answer_dict[i['survey_account_id']]['tuple_key1'] = []
                    answer_dict[i['survey_account_id']]['tuple_key2'] = []
                answer_dict[i['survey_account_id']]['all_score'] += i['score']

                if i['question_id'] == question_ids[0]:
                    answer_dict[i['survey_account_id']]['tuple_key2'] = i['answer_id_lst']
                elif i['question_id'] == question_ids[1]:
                    answer_dict[i['survey_account_id']]['tuple_key1'] = i['answer_id_lst']

            for i in account_ids:
                tuple_key1 = answer_dict[i]['tuple_key1']
                tuple_key2 = answer_dict[i]['tuple_key2']
                for key1 in tuple_key1:
                    try:
                        key1 = int(key1)
                    except ValueError:
                        continue
                    for key2 in tuple_key2:
                        try:
                            key2 = int(key2)
                        except ValueError:
                            continue
                        tuple_key = (key1, key2)
                        answer_dict[i]['tuple_key'].append(tuple_key)

        for i in account_ids:
            account_data = answer_dict[i]
            for j in account_data['tuple_key']:
                data_dict[j]['all_score'] += account_data['all_score']
                data_dict[j]['people_count'] += 1


        data_ls = [['组合选项', '均分', '人数']]
        if group == 'sex':
            for key in data_dict.keys():
                option_dict = option_dict0.get(key[1], {})
                option_name = option_dict.get('num', '未知') + '、' + option_dict.get('name', '未知')
                name = key[0] + '-' + option_name
                people_count =  data_dict[key]['people_count']
                if people_count:
                    avg_score = round(data_dict[key]['all_score'] / data_dict[key]['people_count'], 1)
                else:
                    avg_score = 0
                ls = [name, avg_score, people_count]
                data_ls.append(ls)

        else:
            for key in data_dict.keys():
                option_dict = option_dict0.get(key[1], {})
                option_name = option_dict.get('num', '未知') + '、' + option_dict.get('name', '未知')
                option_dict2 = option_dict1.get(key[0], {})
                option_name2 = option_dict2.get('num', '未知') + '、' + option_dict2.get('name', '未知')
                name = option_name + '-' + option_name2
                people_count =  data_dict[key]['people_count']
                people_count = people_count
                if people_count:
                    avg_score = round(data_dict[key]['all_score'] / data_dict[key]['people_count'], 1)
                else:
                    avg_score = 0
                ls = [name, avg_score, people_count]
                data_ls.append(ls)

        data = {
            'name': '总体的均分',
            'activeName': 'avg-barchart',
            'data': data_ls
        }
        res_data = {'data': data, 'title': f"{survey_obj.name}的组合分析"}
        return Response(res_data, status=status.HTTP_200_OK)

    # 单个问题的分析
    @action(methods=['post'], detail=True, url_path='survey_question_avg_analysis')
    def get_survey_question_avg_analysis(self, request, pk=None):
        survey_obj = self.get_object()

        question_id = request.data.get('question_id', '')
        if not question_id:
            return Response({'message': '缺少参数'}, status=status.HTTP_400_BAD_REQUEST)

        # 问卷问题个数
        category_qs = SurveyQuestionCategory.objects.filter(survey_id=pk).values('id', 'category_name')
        category_ids = []
        category_dict = {}
        category_qt_count = {}
        for cate in category_qs:
            category_ids.append(cate['id'])
            category_dict[cate['id']] = cate['category_name']
            category_qt_count[cate['id']] = 0

        question_qs = SurveyQuestion.objects.filter(survey_qs_cate_id__in=category_ids)\
            .values('id', 'survey_qs_cate_id', 'name')

        question_count = 0  # 题目数
        question_cate_dict = {}
        for qt in question_qs:
            question_count += 1
            question_cate_dict[qt['id']] = qt['survey_qs_cate_id']
            category_qt_count[qt['survey_qs_cate_id']] += 1

        question_op = SurveyQuestionOption.objects.filter(question_id=question_id)\
            .values('id', 'question_id', 'num', 'name')
        options_dict = {}
        option_score = {}
        for op in question_op:
            options_dict[op['id']] = op
            option_score[op['id']] = {}
            option_score[op['id']]['all'] = {}
            option_score[op['id']]['all']['score'] = 0
            option_score[op['id']]['all']['people'] = 0
            for cate_id in category_ids:

                option_score[op['id']][cate_id] = {}
                option_score[op['id']][cate_id]['score'] = 0

        # 填写对象
        faculty_code = request.data.get('faculty_code', '')
        class_code = request.data.get('class_code', '')
        sex = request.data.get('sex', '')

        # 填写对象
        survey_account = SurveyAccount.objects.filter(survey_id=pk, status__in=[1, 2])
        if faculty_code:
            survey_account = survey_account.filter(faculty_code=faculty_code)
        if class_code:
            survey_account = survey_account.filter(class_code=class_code)
        if sex:
            survey_account = survey_account.filter(sex=sex)

        # 填写对象
        survey_account = survey_account.values()

        survey_account_dict = {}
        survey_account_ids = []

        for qs in survey_account:
            survey_account_ids.append(qs['id'])
            survey_account_dict[qs['id']] = {}
            survey_account_dict[qs['id']]['all_score'] = 0
            survey_account_dict[qs['id']]['options'] = []

        survey_answer_qs = SurveyAnswer.objects.filter(survey_account_id__in=survey_account_ids)\
            .values('survey_account_id', 'score', 'question_id', 'answer_id_lst')

        for qs in survey_answer_qs:
            survey_account_dict[qs['survey_account_id']]['all_score'] += qs['score']
            if qs['question_id'] == question_id:
                survey_account_dict[qs['survey_account_id']]['options'] = qs['answer_id_lst']

            cate_id = question_cate_dict[qs['question_id']]
            if cate_id not in survey_account_dict[qs['survey_account_id']]:
                survey_account_dict[qs['survey_account_id']][cate_id] = 0
            survey_account_dict[qs['survey_account_id']][cate_id] += qs['score']

        for account_id in survey_account_ids:
            account_dict = survey_account_dict[account_id]
            op_ls = account_dict['options']
            for op_id in op_ls:
                try:
                    op_id = int(op_id)
                except ValueError:
                    continue
                option_score[op_id]['all']['score'] += account_dict['all_score']
                option_score[op_id]['all']['people'] += 1
                for cate_id in category_ids:
                    option_score[op_id][cate_id]['score'] += account_dict[cate_id]


        all_avg = [['选项', '均分', '人数']]
        cate_ls = ['选项']
        for cate_id in category_ids:
            cate_ls.append(category_dict[cate_id])
        cate_avg = [cate_ls]
        for key in option_score.keys():
            op_dict = options_dict[key]
            op_name = op_dict.get('num', '未知') + '、' + op_dict.get('name', '未知')
            all_score = option_score[key]['all']['score']
            all_people = option_score[key]['all']['people']
            if all_people:
                avg_score = round(all_score / (all_people * question_count), 1)
            else:
                avg_score = 0
            all_data = [op_name, avg_score, all_people]
            all_avg.append(all_data)
            cate_data = [op_name]
            for cate_id in category_ids:
                cate_score = option_score[key][cate_id]['score']
                cate_question = category_qt_count[cate_id]
                if all_people:
                    cate_score_avg = round(cate_score / (all_people * cate_question), 1)
                else:
                    cate_score_avg = 0
                cate_data.append(cate_score_avg)
            cate_avg.append(cate_data)

        data = [
            {
                'id': 'all',
                'activeName': 'all-barchart',
                'name': '总体的均分',
                'data': all_avg
            },
            {
               'id': 'cate',
               'activeName': 'cate-barchart',
               'name': '各个维度的均分',
               'data': cate_avg
            }
        ]
        res_data = {'data': data, 'title': f"{survey_obj.name}的单个问题分析"}
        return Response(res_data, status=status.HTTP_200_OK)